package com.emuporium.gymrat.server.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class LogDB {

	public static int deleteLogSet(int setId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "delete from logsets where _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, setId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return 1;
	}

	public static int deleteLogEntry(int logId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "delete from logentries where _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, logId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return 1;
	}

	public static int insertLog(int routineId, int entryId, int exerciseId,
			int variationId, int equipmentId, Date date,
			ArrayList<Integer> reps, ArrayList<Integer> weight, int accountId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		Statement s = null;
		ResultSet rs = null;
		int insertId;

		String query = "insert into logentries (routineId, entryid, exerciseid, variationid, equipmentid, timeworked,accountid)\r\n"
				+ "values (?, ?, ?, ?, ?, ?,?);";
		String lastInsert = "select last_insert_id()";

		String query2 = "insert into logsets (logid, reps, weight)\r\n"
				+ "values (?,?,?)";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, routineId);
			ps.setInt(2, entryId);
			ps.setInt(3, exerciseId);
			ps.setInt(4, variationId);
			ps.setInt(5, equipmentId);
			if (date != null)
				ps.setDate(6, date);
			else
				ps.setDate(6, new Date(System.currentTimeMillis()));
			ps.setInt(7, accountId);

			ps.executeUpdate();

			s = connection.createStatement();
			rs = s.executeQuery(lastInsert);
			rs.first();
			insertId = rs.getInt("last_insert_id()");

			for (int i = 0; i < reps.size(); i++) {
				ps = connection.prepareStatement(query2);
				ps.setInt(1, insertId);
				ps.setInt(2, reps.get(i));
				ps.setInt(3, weight.get(i));
				ps.executeUpdate();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return 0;
	}

	public static ArrayList<LogEntry> selectLogEntries(int accountId) {
		return selectLogEntries(accountId, -1, null, null, -1, -1);
	}

	public static ArrayList<LogEntry> selectLogEntries(int accountId, int logId) {
		return selectLogEntries(accountId, logId, null, null, -1, -1);
	}
	
	public static ArrayList<LogEntry> selectLogEntries(int accountId,
			Date start, Date end, int routineId, int exerciseId) {
		return selectLogEntries(accountId, -1, start, end, routineId,
				exerciseId);
	}

	private static ArrayList<LogEntry> selectLogEntries(int accountId,
			int logId, Date startDate, Date endDate, int routineId,
			int exerciseId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<LogEntry> result = new ArrayList<LogEntry>();
		boolean useLogId = logId > 0;
		boolean useStartDate = startDate != null;
		boolean useEndDate = endDate != null;
		boolean useRoutine = routineId > 0;
		boolean useRoutineNull = routineId == 0;
		boolean useExercise = exerciseId > 0;
		int param = 1;

		String query = "select logentries._id AS \'logid\', exercises._id AS \'exerciseid\',exercises.name AS \'exercise\',\r\n"
				+ "variations._id AS \'variationid\', variations.variation, equipment._id AS \'equipmentid\',\r\n"
				+ "equipment.equipment, routines._id AS \'routineid\', routines.name AS \'routine\', timeworked \r\n"
				+ "from logentries\r\n"
				+ "join exercises on (logentries.exerciseid = exercises._id)\r\n"
				+ "left join variations on (logentries.variationid = variations._id)\r\n"
				+ "left join equipment on (logentries.equipmentid = equipment._id)\r\n"
				+ "left join routines on (logentries.routineid = routines._id)\r\n"
				+ "where logentries.accountid = ? ";

		String queryLogId = "AND logentries._id = ? ";
		String queryDates = "AND timeworked between ? AND ? ";
		String queryRoutine = "AND routines._id = ? ";
		String queryRoutineNull = "AND routines._id IS NULL ";
		String queryExercise = "AND exercises._id = ? ";
		String queryOrder = "ORDER BY timeworked DESC, logentries._id";

		if (useLogId)
			query = query + queryLogId;

		query = query + queryDates;

		if (useRoutine)
			query = query + queryRoutine;
		if (useRoutineNull)
			query += queryRoutineNull;
		if (useExercise)
			query = query + queryExercise;

		query += queryOrder;

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(param++, accountId);
			if (useLogId)
				ps.setInt(param++, logId);
			if (useStartDate)
				ps.setDate(param++, startDate);
			else
				ps.setDate(param++, new Date(0));
			if (useEndDate)
				ps.setDate(param++, endDate);
			else
				ps.setDate(param++, new Date(System.currentTimeMillis()));
			if (useRoutine)
				ps.setInt(param++, routineId);			
			if (useExercise)
				ps.setInt(param++, exerciseId);
			rs = ps.executeQuery();

			while (rs.next()) {
				LogEntry entry = new LogEntry();
				entry.setId(rs.getInt("logid"));
				entry.setRoutineId(rs.getInt("routineid"));
				entry.setRoutineName(rs.getString("routine"));
				entry.setExerciseId(rs.getInt("exerciseid"));
				entry.setExerciseName(rs.getString("exercise"));
				entry.setVariationId(rs.getInt("variationid"));
				entry.setVariationName(rs.getString("variation"));
				entry.setEquipmentId(rs.getInt("equipmentid"));
				entry.setEquipmentName(rs.getString("equipment"));
				entry.setDateWorked(rs.getDate("timeworked"));
				entry.setSets(selectLogSets(entry.getId()));
				result.add(entry);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static ArrayList<LogSet> selectLogSets(int logId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		ArrayList<LogSet> result = new ArrayList<LogSet>();

		String query = "select _id, reps, weight\r\n"
				+ "from logsets where logid = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, logId);
			rs = ps.executeQuery();

			while (rs.next()) {
				LogSet set = new LogSet();
				set.setId(rs.getInt("_id"));
				set.setReps(rs.getInt("reps"));
				set.setWeight(rs.getInt("weight"));

				result.add(set);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}
		return result;
	}

	public static int updateLogEntry(int logId, Date date, int variationId,
			int equipmentId) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "update logentries "
				+ "set timeworked = ?, variationid = ?,equipmentid = ? "
				+ "where _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setDate(1, date);
			ps.setInt(2, variationId);
			ps.setInt(3, equipmentId);
			ps.setInt(4, logId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return 1;
	}

	public static int updateLogSet(int setId, int reps, int weight) {
		ConnectionPool pool = ConnectionPool.getInstance();
		Connection connection = pool.getConnection();
		PreparedStatement ps = null;

		String query = "update logsets " + "set reps = ?, weight = ? "
				+ "where _id = ?";

		try {
			ps = connection.prepareStatement(query);
			ps.setInt(1, reps);
			ps.setInt(2, weight);
			ps.setInt(3, setId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closePreparedStatement(ps);
			pool.freeConnection(connection);
		}

		return 1;
	}

}
